package com.bh.db.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import oracle.jdbc.driver.OracleTypes;

public class Conn {

        /**
         * @param args
         */
        //类的路径和名称
        private final static String CLS_STRING=Conn.class.getName();
        /*Oracle数据库连接URL  192.168.0.250:1521:hywh  127.0.0.1:6002:sghyjk*/
        private final static String DB_URL="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dsjc";
        /* Oracle数据库连接驱动 oracle.jdbc.driver.OracleDriver*/
        private final static String DB_DRIVER="com.microsoft.jdbc.sqlserver.SQLServerDriver";
        /*数据库用户名*hywh  jskj */
        private final static String DB_USER="dsjc";
        /* 数据库密码*hywh    jskj5998*/
        private final static String DB_PWD="dsjc";
        
        private Connection connection=null;
        private Statement statement=null;
        private PreparedStatement pStatement=null;
        private ResultSet resultSet=null;
        private CallableStatement cStatement=null;
        
        static Logger log=Logger.getLogger(Conn.class);
        
        /**
         * 构造即打开数据库连接
         */
        public Conn(){
                /*初始化Log*/
                
                
                
                //使用数据连接池
                try {
                        Context initCtx= new InitialContext();
                        DataSource dSource= (DataSource)initCtx.lookup("java:comp/env/jdbc/oracle/sghyjk");
                        connection=dSource.getConnection();
                        if (connection!=null) {
                                //System.out.println(CLS_STRING+"创建数据库连接成功");
                        }else {
                                System.out.println(CLS_STRING+"创建数据库连接失败");
                        }
                } catch (Exception e) {
                        System.out.println(CLS_STRING+"创建数据库连接失败，原因："+e.getMessage());
                }
        }
        public Conn(String sType){
                // TODO构造函数连接数据库
                if (sType.equals("driver")) {
                        /**************注册驱动方式连接数据库*/
                        try {
                                Class.forName(DB_DRIVER);
                                try {
                                        connection=DriverManager.getConnection(DB_URL,DB_USER,DB_PWD);
                                } catch (SQLException e) {
                                        // TODO: handle exception
                                        System.out.println(CLS_STRING+"创建数据库连接失败，原因："+e.getMessage());
                                }
                                if (connection!=null) {
                                        System.out.println(CLS_STRING+"创建数据库连接成功");
                                        
                                }
                                
                        } catch (Exception e) {
                                // TODO: handle exception
                                System.out.println(CLS_STRING +"没有发现" +DB_DRIVER);
                        }
                }
                if (sType.equals("pool")) {
                        //使用数据连接池
                        try {
                                Context initCtx= new InitialContext();
                                DataSource dSource= (DataSource)initCtx.lookup("java:comp/env/jdbc/oracle/sghyjk");
                                connection=dSource.getConnection();
                                if (connection!=null) {
                                        System.out.println(CLS_STRING+"创建数据库连接成功");
                                }else {
                                        System.out.println(CLS_STRING+"创建数据库连接失败");
                                }
                        } catch (Exception e) {
                                System.out.println(CLS_STRING+"创建数据库连接失败，原因："+e.getMessage());
                        }
                }
        }
/**
 * 更新、插入数据
 * @param sSQL
 * @return
 */
        public int ExecuteSQL(String sSQL)  {
                int iRet=0;
                pStatement=null;
                resultSet=null;
                try {
                        pStatement=connection.prepareStatement(sSQL);
                        iRet=pStatement.executeUpdate();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        System.out.println(CLS_STRING+"插入或更新语句失败,语句："+sSQL+",原因："+e.getMessage());
                        iRet=-1;
                }
                return iRet;
                
        }
        /**
         * 查询数据
         * @param sSQL
         * @return
         */
        public ResultSet SelectSQL(String sSQL) {
                pStatement=null;
                resultSet=null;
                try {
                        pStatement=connection.prepareStatement(sSQL);
                        resultSet=pStatement.executeQuery();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        System.out.println(CLS_STRING+"查询语句失败,语句："+sSQL+",原因："+e.getSQLState());
                }
                return resultSet;
                
        }
        /**
         * 返回数据条数
         * @param sSQL
         * @return
         */
        public int SelectSQLCount(String sSQL) {
                int rsCount=0;
                //String str="SELECT COUNT(*)AS KK  FROM ("+sSQL+")";
                //System.out.println("sSQL======="+sSQL);
                String str=sSQL.replaceAll("(?i)SELECT.*?(?i)FROM", "SELECT COUNT(*) AS KK FROM"); //忽略大小写正则表达式
                //System.out.println("str====="+str);
                ResultSet rSet=SelectSQL(str);
                try {
                        while (rSet.next()) {
                                rsCount=rSet.getInt("KK");
                        }
                        rSet.close();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        System.out.println(CLS_STRING+"查询语句失败,语句："+str+",原因："+e.toString());
                }
                return rsCount;
        }
        /**
         * 返回合计数量
         * @param sSQL  要查询的sql语句
         * @param sSQLSum       要返回的合计数据列名
         * @return      double合计
         */
        public double SelectSQLSum(String sSQL,String sSQLSum){
                double dblSum=0;
                String str="SELECT SUM("+sSQLSum+")AS KK  FROM ("+sSQL+")";
                ResultSet rSet=SelectSQL(str);
                try {
                        while (rSet.next()) {
                                dblSum=rSet.getDouble("KK");
                        }
                        rSet.close();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        System.out.println(CLS_STRING+"查询语句失败,语句："+str+",原因："+e.toString());
                }
                return dblSum;
        }
        /**
         * 执行存储过程
         * @param sProc 存储过程"{call Proc(?,?)}"
         * @return      CallableStatement
         */
        public CallableStatement executeCS(String sProc){
                try {
                        connection.setAutoCommit(false);
                        cStatement=connection.prepareCall(sProc);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        System.out.println(CLS_STRING+"调用存储过程失败,语句："+sProc+",原因："+e.toString());
                }
                return cStatement;
        }
        /**
         * 设置是否自动提交
         * @param f
         */
        public void setAutoCommit(boolean f){
                try {
                        connection.setAutoCommit(f);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        System.out.println(CLS_STRING+"设置是否自动提交失败,原因："+e.toString());
                }
        }
        /**
         * 使用存储过程需要调用此提交
         */
        public void Commit(){
                try {
                        connection.commit();
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        System.out.println(CLS_STRING+"commit提交失败,原因："+e.toString());
                }
        }
        /**
         * 释放数据库连接
         */
        public void Close() {
                if (resultSet!=null) {
                        try {
                                resultSet.close();
                                resultSet=null;
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                System.out.println(CLS_STRING+"释放记录集失败,原因："+e.toString());
                        }
                }
                if (statement!=null) {
                        try {
                                statement.close();
                                statement=null;
                        } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                System.out.println(CLS_STRING+"释放Statement失败,原因："+e.toString());
                        }
                }
                if (connection!=null) {
                        try {
                                connection.close();
                                connection=null;
                        } catch (SQLException e) {
                                // TODO: handle exception
                                System.out.println(CLS_STRING+"关闭数据库连接失败,原因："+e.toString());
                        }
                }
                //System.out.println(CLS_STRING+"关闭数据库连接成功!");
        }
        public static void main(String[] args) {
                // TODO Auto-generated method stub

                ResultSet rSet=null;
                Conn dOraConn=new Conn("driver");
                
                CallableStatement cs=null;
                
                try {
                        cs=dOraConn.executeCS("{call P_SELECTALL(?,?,?)}");
                        cs.setString(1, "select * from dm_hy WHERE hydl_dm='15'");
                        cs.registerOutParameter(2, OracleTypes.INTEGER);
                        cs.registerOutParameter(3, OracleTypes.CURSOR);
                        cs.execute();
                        rSet=(ResultSet)cs.getObject(3);
                        int iCount=cs.getInt(2);
                        if (iCount>0) {
                                System.out.println("记录数："+iCount);
                                while (rSet.next()) {
                                        System.out.println("记录集："+rSet.getString(1)+"   "+rSet.getString(2)+"   "+rSet.getString(3));
                                }
                        }
                } catch (SQLException e1) {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                }
                
                dOraConn.Close();
        }

}

